using System;
using System.Configuration;
using System.Diagnostics;
using System.ComponentModel;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Media;
using System.Text;
using System.Collections;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Management;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Net.Mail;
using System.IO;
using System.Collections.Generic;

public class Utility
{      
    public Utility()
    {
    }    
    public static string GetWindowsVersion(string strInstanceName)
    {
        const string WINDOWS_VERSION_QUERY = "Select * From Win32_OperatingSystem WHERE Primary = True"; // Primary - specifies the primary OS
        string version = string.Empty;

        try
        {
            ObjectQuery objectQuery = new ObjectQuery(WINDOWS_VERSION_QUERY);

            System.Management.ManagementScope managementScope = new System.Management.ManagementScope(@"\\" + strInstanceName.ToString() + "\\root\\cimv2");
            managementScope.Connect();

            ManagementObjectSearcher managementObjectSearcher = new ManagementObjectSearcher(managementScope, objectQuery);
            ManagementObjectCollection managementObjectCollection = managementObjectSearcher.Get();

            foreach (ManagementObject proc in managementObjectCollection)
            {
                if (proc != null)
                {
                    string caption = proc["Caption"].ToString();
                    string servicePack = Convert.ToString(proc["CSDVersion"]);
                    version = caption.ToString() + " " + servicePack.ToString();
                    break;
                }
            }
        }
        catch { }

        return version;

    }
    public static string DBANotificationEMailString()
    {
        Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");
        string sqlQuery = "DECLARE @DBANotificationEmailString varchar(2000) SELECT @DBANotificationEmailString = COALESCE(@DBANotificationEmailString + ', ', '') + CAST(DBAContactEmail AS varchar(200)) FROM DBAContact WHERE DBANotificationFlag = 1 SELECT @DBANotificationEmailString as 'DBANotificationEmailString'";
        string strDBANotificationEmail = db.ExecuteScalar(CommandType.Text, sqlQuery).ToString();
        return (strDBANotificationEmail);
    }
    public static string DBAOnCallNotificationEMailString()
    {
        Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");
        string sqlQuery = "DECLARE @OnCallDBANotificationEmailString varchar(2000) SELECT @OnCallDBANotificationEmailString = COALESCE(@OnCallDBANotificationEmailString + ', ', '') + CAST(DBAOnCallContactEmail AS varchar(200)) FROM DBAContact WHERE DBAOnCallFlag = 1 SELECT @OnCallDBANotificationEmailString as 'OnCallDBANotificationEmailString'";
        string strDBANotificationEmail = db.ExecuteScalar(CommandType.Text, sqlQuery).ToString();
        return (strDBANotificationEmail);
    }
    public static string DatabaseContactEMailByInstanceString(string strInstanceName)
    {
        Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");
        string sqlQuery = "DECLARE @TableVar table (PrimaryContactEmail varchar(5000) NOT NULL) " +
                          "INSERT INTO @TableVar SELECT PrimaryContactEmail FROM [Database] WHERE InstanceName = '" + strInstanceName.ToString() + "' " +
                          "GROUP BY PrimaryContactEmail UNION " +
                          "SELECT PrimaryContactEmail FROM [Instance] WHERE InstanceName = '" + strInstanceName.ToString() + "' " +
                          "DECLARE @DatabaseContactEMailByInstanceString varchar(5000) " +
                          "SELECT @DatabaseContactEMailByInstanceString = COALESCE(@DatabaseContactEMailByInstanceString + ', ', '') + CAST(PrimaryContactEmail AS varchar(500)) " +
                          "FROM @TableVar SELECT @DatabaseContactEMailByInstanceString as 'DatabaseContactEMailByInstanceString'";

        string strDatabaseContactEMailByInstanceString = db.ExecuteScalar(CommandType.Text, sqlQuery).ToString();
        return (strDatabaseContactEMailByInstanceString);
    }
    public static void LoadRepository(string strInstanceList)
    {
        Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");
        string connectionString = ConfigurationManager.AppSettings["Application_DataConnString"];

        String strTimeDisplay1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1DisplayName'").ToString();
        String strTimeDisplay2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2DisplayName'").ToString();
        String strTimeDisplay3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3DisplayName'").ToString();
        String strTimeDisplay4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4DisplayName'").ToString();

        String strTimeZone1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1TimeZoneID'").ToString();
        String strTimeZone2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2TimeZoneID'").ToString();
        String strTimeZone3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3TimeZoneID'").ToString();
        String strTimeZone4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4TimeZoneID'").ToString();

        DateTimeOffset nowDateTime = DateTimeOffset.Now;
        DateTimeOffset Time1DateTime = System.TimeZoneInfo.ConvertTime(nowDateTime, System.TimeZoneInfo.FindSystemTimeZoneById(strTimeZone1.ToString()));
        DateTimeOffset Time2DateTime = System.TimeZoneInfo.ConvertTime(nowDateTime, System.TimeZoneInfo.FindSystemTimeZoneById(strTimeZone2.ToString()));
        DateTimeOffset Time3DateTime = System.TimeZoneInfo.ConvertTime(nowDateTime, System.TimeZoneInfo.FindSystemTimeZoneById(strTimeZone3.ToString()));
        DateTimeOffset Time4DateTime = System.TimeZoneInfo.ConvertTime(nowDateTime, System.TimeZoneInfo.FindSystemTimeZoneById(strTimeZone4.ToString()));

        SqlConnection cn = new SqlConnection(connectionString);              
        cn.Open();

        // create the DataSet
        DataSet ds = new DataSet();

        if (strInstanceList == "All")
        {
           string EnableAlerts = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config WITH(NOLOCK) Where ConfigName ='EnableAlerts'").ToString();

           SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT InstanceName FROM Instance WITH(NOLOCK) WHERE LoadRepository = 'True' AND LastLoadRepositoryEnd < DATEADD(hour, -3, GETDATE()) ORDER BY LastLoadRepositoryStart", cn);
           adapter.Fill(ds);
                        
        }
        else
        {
            SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT '" + strInstanceList.ToUpper().ToString() +"' AS InstanceName", cn);
            adapter.Fill(ds);
        }

        DataTable dt = ds.Tables[0];

        //For Each Instance
        foreach (DataRow row in dt.Rows)
        {
            SqlCommand cmdUpdateLastLoadRepositoryStart = new SqlCommand();
            cmdUpdateLastLoadRepositoryStart.CommandTimeout = 300;
            cmdUpdateLastLoadRepositoryStart.Connection = cn;

            cmdUpdateLastLoadRepositoryStart.CommandText = "UPDATE [Instance] SET LastLoadRepositoryStart = getdate() WHERE InstanceName = '" + row["InstanceName"].ToString() + "'";
            cmdUpdateLastLoadRepositoryStart.ExecuteNonQuery();                       

            try
            {
                string MachineName = String.Empty;
                if (row["InstanceName"].ToString().Contains(@"\"))
                {
                    Int32 intLocation = row["InstanceName"].ToString().IndexOf(@"\");
                    MachineName = row["InstanceName"].ToString().Substring(0, intLocation);
                }
                else
                {
                    MachineName = row["InstanceName"].ToString();
                }

                System.Management.ManagementScope oMs = new System.Management.ManagementScope(@"\\" + MachineName.ToString() + "\\root\\cimv2");
                oMs.Options.Timeout = new TimeSpan(0, 0, 0, 600, 0);
                oMs.Connect();

                //DriveUsage
                System.Management.ObjectQuery oQuery1 = new ObjectQuery("SELECT Size, FreeSpace, Name, FileSystem FROM Win32_LogicalDisk WHERE DriveType = 3");

                ManagementObjectSearcher oSearcher1 = new ManagementObjectSearcher(oMs, oQuery1);
                ManagementObjectCollection collection1 = oSearcher1.Get();

                //Delete the records in DriveSpaceUsage
                SqlCommand cmdDeleteDriveSpace = cn.CreateCommand(); 
                cmdDeleteDriveSpace.CommandText = "DELETE FROM DriveSpaceUsage Where InstanceName = '" + row["InstanceName"].ToString() + "'";
                cmdDeleteDriveSpace.ExecuteNonQuery();

                //Insert the records for DriveSpaceUsage
                foreach (ManagementObject res in collection1)
                {
                    string DriveName = res["Name"].ToString();
                    decimal DriveSizeGB = Convert.ToDecimal(res["Size"]) / 1024 / 1024 / 1024;
                    //Fix Divide By 0 Errors
                    if (DriveSizeGB < 1)
                    {
                        DriveSizeGB = 1;
                    }
                    decimal DriveFreeSpaceGB = Convert.ToDecimal(res["FreeSpace"]) / 1024 / 1024 / 1024;
                    if (DriveFreeSpaceGB < 1)
                    {
                        DriveFreeSpaceGB = 1;
                    }

                    decimal DriveFreeSpacePct = Decimal.Round(DriveFreeSpaceGB / DriveSizeGB, 3) * 100;

                    //Update the Repository
                    SqlCommand cmdLoadDriveSpace = cn.CreateCommand(); //cn is referencing the repository instance
                    cmdLoadDriveSpace.CommandText = "INSERT INTO DriveSpaceUsage (InstanceName,DriveName,DriveFreeSpaceGB,DriveSizeGB,DriveFreeSpacePct) Values ('" + row["InstanceName"].ToString() + "','" + DriveName.ToString() + "'," + DriveFreeSpaceGB + "," + DriveSizeGB + "," + DriveFreeSpacePct + ")";
                    cmdLoadDriveSpace.ExecuteNonQuery();

                    //Log Alert if space below 5% 
                    if (DriveFreeSpacePct <= 5)
                    {
                        if (DriveName == "C:")
                        {
                            if (DriveFreeSpaceGB < 1)
                            {
                                DbCommand dbCommand1 = db.GetSqlStringCommand("if exists(SELECT AlertID FROM Alert WHERE AlertInstanceName='" + row["InstanceName"].ToString() + "' and AlertName ='LOW DISK ALERT'  AND AlertStatus IN ('OPEN','ACKNOWLEDGED')) SELECT 'false' else SELECT 'true'");

                                string retValue = Convert.ToString(db.ExecuteScalar(dbCommand1));
                                if (retValue == "true")
                                {
                                    DBANotificationAlert("LOW DISK ALERT: " + DriveName.ToString(), row["InstanceName"].ToString(), "High");
                                }
                                SqlCommand cmdLOWDISK = cn.CreateCommand();
                                cmdLOWDISK.CommandText = @"exec iuAlert @AlertPriority='High',@AlertInstanceName='" + row["InstanceName"].ToString() + "',@AlertName='" + DriveName.ToString() + " LOW DISK ALERT'";
                                cmdLOWDISK.ExecuteNonQuery();
                                //break; 
                            }
                        }
                        if (DriveName == "Y:")
                        {
                       
                        }
                        else
                        {
                            DbCommand dbCommand1 = db.GetSqlStringCommand("if exists(SELECT AlertID FROM Alert WHERE AlertInstanceName='" + row["InstanceName"].ToString() + "' and AlertName ='LOW DISK ALERT'  AND AlertStatus IN ('OPEN','ACKNOWLEDGED')) SELECT 'false' else SELECT 'true'");
                            string retValue = Convert.ToString(db.ExecuteScalar(dbCommand1));
                            if (retValue == "true")
                            {
                                DBANotificationAlert("LOW DISK ALERT: " + DriveName.ToString(), row["InstanceName"].ToString(), "High");
                            }
                            SqlCommand cmdLOWDISK = cn.CreateCommand();
                            cmdLOWDISK.CommandText = @"exec iuAlert @AlertPriority='High',@AlertInstanceName='" + row["InstanceName"].ToString() + "',@AlertName='" + DriveName.ToString() + " LOW DISK ALERT'";
                            cmdLOWDISK.ExecuteNonQuery();
                        }
                    }
                } 

                //Virtual or Physical
                System.Management.ObjectQuery oQuery2 = new ObjectQuery("Select * from Win32_BaseBoard");

                ManagementObjectSearcher oSearcher2 = new ManagementObjectSearcher(oMs, oQuery2);
                ManagementObjectCollection oCollection2 = oSearcher2.Get();

                string strIsVirtual = "0";

                foreach (ManagementObject objItem in oCollection2)
                {
                    strIsVirtual = objItem["Manufacturer"].ToString();

                    if (objItem["Manufacturer"].ToString() == "Microsoft Corporation" | objItem["Manufacturer"].ToString() == "Intel Corporation")
                    {
                        strIsVirtual = "1";
                    }
                    else
                    {
                        strIsVirtual = "0";
                    }
                }

                //Processor
                System.Management.ObjectQuery oQuery = new System.Management.ObjectQuery("SELECT * FROM Win32_Processor");

                //Execute the query  
                ManagementObjectSearcher oSearcher = new ManagementObjectSearcher(oMs, oQuery);

                //Get the results
                ManagementObjectCollection oReturnCollection = oSearcher.Get();
                string strArchitecture = "";
                string strProcessorName = "";
                string strMaxClockSpeed = "";

                foreach (ManagementObject oReturn in oReturnCollection)
                {
                    strArchitecture = oReturn["Architecture"].ToString();
                    switch (strArchitecture)
                    {
                        case "0":
                            strArchitecture = "x86";
                            break;
                        case "1":
                            strArchitecture = "MIPS";
                            break;
                        case "2":
                            strArchitecture = "Alpha";
                            break;
                        case "3":
                            strArchitecture = "PowerPC";
                            break;
                        case "6":
                            strArchitecture = "Intel Itanium Processor Family (IPF)";
                            break;
                        case "9":
                            strArchitecture = "x64";
                            break;
                        default:
                            strArchitecture = "Unknown";
                            break;
                    }
                    strProcessorName = oReturn["Name"].ToString();
                    strMaxClockSpeed = oReturn["MaxClockSpeed"].ToString();

                    //Get Trace Flags
                    string strTraceFlags = "";
                    try
                    {
                        StringBuilder sbTF = new StringBuilder();
                        sbTF.Append(@"SET NOCOUNT ON CREATE TABLE #TraceFlag([TraceFlag] VARCHAR(10) NOT NULL,[Status] VARCHAR(10) NOT NULL,[Global] VARCHAR(10) NOT NULL,[Session] VARCHAR(10) NOT NULL) INSERT INTO #TraceFlag EXEC('DBCC TRACESTATUS (-1)')DECLARE @TraceFlag VARCHAR(1000) SELECT @TraceFlag = COALESCE(@TraceFlag + ',', '') + TraceFlag FROM #TraceFlag SELECT TraceFlags = @TraceFlag DROP TABLE #TraceFlag ");

                        SqlConnection connTF = new SqlConnection(@"server=" + row["InstanceName"].ToString() + ";database=master;Integrated Security=SSPI;Pooling=False");

                        SqlDataAdapter aTF = new SqlDataAdapter(sbTF.ToString(), connTF);
                        DataSet sTF = new DataSet();
                        aTF.Fill(sTF);

                        DataRow drTF = sTF.Tables[0].Rows[0];

                        strTraceFlags = drTF["TraceFlags"].ToString();

                        if (strTraceFlags == "")
                        {
                            strTraceFlags = "None";
                        }

                        connTF.Close();
                    }
                    catch
                    {
                        strTraceFlags = "None";
                    }        

                    //Connect to the instance and get the IP Address and Port Number and Server Properties
                    StringBuilder sb = new StringBuilder();
                    sb.Append(@"set nocount on DECLARE @key varchar(100),@key2 VARCHAR(100),@PortNumber varchar(20),@ip varchar(200), @Domain VARCHAR(200), @ipLine varchar(200),@pos int if charindex('\',CONVERT(char(20), SERVERPROPERTY('servername')),0) <>0 begin set @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'+@@servicename+'\MSSQLServer\Supersocketnetlib\TCP' end else begin  set @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\TCP' end set @key2 = 'SYSTEM\ControlSet001\Services\Tcpip\Parameters\' EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@key,@value_name='Tcpport',@value=@PortNumber OUTPUT EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key2=@key2,@value_name='Domain',@value=@Domain OUTPUT set @ip = NULL Create table #temp (ipLine varchar(200)) Insert #temp exec master..xp_cmdshell 'ipconfig' select @ipLine = ipLine from #temp where upper (ipLine) like '%IP ADDRESS%' or upper (ipLine) like '%IPv4%' if (isnull (@ipLine,'***') != '***') begin set @pos = CharIndex (':',@ipLine,1); set @ip = rtrim(ltrim(substring (@ipLine, @pos + 1 ,len (@ipLine) - @pos))) end SELECT CONVERT(char(20), SERVERPROPERTY('servername')) instancename, CONVERT(varchar(50),@ip) IPAddress, CONVERT(varchar(10),@PortNumber) PortNumber,UPPER(@Domain) Domain drop table #temp set nocount off ");

                    SqlConnection conn1 = new SqlConnection(@"server=" + row["InstanceName"].ToString() + ";database=master;Integrated Security=SSPI;Pooling=False");               

                    SqlDataAdapter a1 = new SqlDataAdapter(sb.ToString(), conn1);
                    DataSet s1 = new DataSet();
                    a1.Fill(s1);
                    DataRow dr1 = s1.Tables[0].Rows[0];

                    //Get Server Properties
                    //SQL 2005 & 2008
                    //SELECT SERVERPROPERTY ('Collation') AS 'Collation',SERVERPROPERTY ('Edition') AS 'Edition',SERVERPROPERTY ('IsClustered') AS 'IsClustered',SERVERPROPERTY ('ProductVersion') AS 'ProductVersion',SERVERPROPERTY ('ProductLevel') AS 'ProductLevel',CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows' WHEN 0 THEN 'Mixed' END AS 'InstanceLoginMode', cpu_count AS CoreCount, cpu_count/hyperthread_ratio AS SocketCount, CEILING(physical_memory_in_bytes/1048576.0) AS 'PhysicalMemory' FROM sys.dm_os_sys_info 
                    //SQL2012
                    //SELECT SERVERPROPERTY ('Collation') AS 'Collation',SERVERPROPERTY ('Edition') AS 'Edition',SERVERPROPERTY ('IsClustered') AS 'IsClustered',SERVERPROPERTY ('ProductVersion') AS 'ProductVersion',SERVERPROPERTY ('ProductLevel') AS 'ProductLevel',CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows' WHEN 0 THEN 'Mixed' END AS 'InstanceLoginMode', cpu_count AS CoreCount, cpu_count/hyperthread_ratio AS SocketCount, physical_memory_kb/1024 AS 'PhysicalMemory' FROM sys.dm_os_sys_info 
                    SqlDataAdapter a6 = new SqlDataAdapter("SELECT CONVERT(int, SUBSTRING(CONVERT(varchar(15), SERVERPROPERTY('productversion')), 0, CHARINDEX('.', CONVERT(varchar(15), SERVERPROPERTY('productversion')))))", conn1);
                    DataSet s6 = new DataSet();
                    a6.Fill(s6);
                    DataRow dr6 = s6.Tables[0].Rows[0];

                    string strInstanceVersion ="";
                    string strInstanceCollation = "";
                    string strInstanceEdition ="";
                    string strInstanceServicePack = "";
                    string strInstanceLoginMode = "";
                    string strInstanceMemory = "";
                    string strInstanceCoreCount = "";
                    string strInstanceSocketCount = "";
                    string strInstanceIsClustered = "";

                    //SQL 2005 & SQL2008
                    if (Convert.ToInt32(dr6[0].ToString()) < 11)
                    {   
                        SqlDataAdapter a3 = new SqlDataAdapter("SELECT SERVERPROPERTY ('Collation') AS 'Collation',SERVERPROPERTY ('Edition') AS 'Edition',SERVERPROPERTY ('IsClustered') AS 'IsClustered',SERVERPROPERTY ('ProductVersion') AS 'ProductVersion',SERVERPROPERTY ('ProductLevel') AS 'ProductLevel',CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows' WHEN 0 THEN 'Mixed' END AS 'InstanceLoginMode', cpu_count AS CoreCount, cpu_count/hyperthread_ratio AS SocketCount, CEILING(physical_memory_in_bytes/1048576.0) AS 'PhysicalMemory' FROM sys.dm_os_sys_info", conn1);
                        DataSet s3 = new DataSet();
                        a3.Fill(s3);
                        DataRow dr3 = s3.Tables[0].Rows[0];

                        strInstanceVersion = dr3[3].ToString();
                        strInstanceCollation = dr3[0].ToString();
                        strInstanceEdition = dr3[1].ToString();
                        strInstanceServicePack = dr3[4].ToString();
                        strInstanceLoginMode = dr3[5].ToString();
                        strInstanceMemory = dr3[8].ToString();
                        strInstanceCoreCount = dr3[6].ToString();
                        strInstanceSocketCount = dr3[7].ToString();
                        strInstanceIsClustered = dr3[2].ToString();
                    }
                    else //SQL2012+
                    {   
                        SqlDataAdapter a3 = new SqlDataAdapter("SELECT SERVERPROPERTY ('Collation') AS 'Collation',SERVERPROPERTY ('Edition') AS 'Edition',SERVERPROPERTY ('IsClustered') AS 'IsClustered',SERVERPROPERTY ('ProductVersion') AS 'ProductVersion',SERVERPROPERTY ('ProductLevel') AS 'ProductLevel',CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows' WHEN 0 THEN 'Mixed' END AS 'InstanceLoginMode', cpu_count AS CoreCount, cpu_count/hyperthread_ratio AS SocketCount, physical_memory_kb/1024 AS 'PhysicalMemory' FROM sys.dm_os_sys_info", conn1);
                        DataSet s3 = new DataSet();
                        a3.Fill(s3);
                        DataRow dr3 = s3.Tables[0].Rows[0];

                        strInstanceVersion = dr3[3].ToString();
                        strInstanceCollation = dr3[0].ToString();
                        strInstanceEdition = dr3[1].ToString();
                        strInstanceServicePack = dr3[4].ToString();
                        strInstanceLoginMode = dr3[5].ToString();
                        strInstanceMemory = dr3[8].ToString();
                        strInstanceCoreCount = dr3[6].ToString();
                        strInstanceSocketCount = dr3[7].ToString();
                        strInstanceIsClustered = dr3[2].ToString();
                    }

                    SqlDataAdapter a4 = new SqlDataAdapter("SELECT value as InstanceMinMemory FROM sys.configurations WHERE name = 'min server memory (MB)'", conn1);
                    DataSet s4 = new DataSet();
                    a4.Fill(s4);
                    DataRow dr4 = s4.Tables[0].Rows[0];

                    SqlDataAdapter a5 = new SqlDataAdapter("SELECT value as InstanceMaxMemory FROM sys.configurations WHERE name = 'max server memory (MB)'", conn1);
                    DataSet s5 = new DataSet();
                    a5.Fill(s5);
                    DataRow dr5 = s5.Tables[0].Rows[0]; 

                    //Get Service Account Information
                    string strSQLServerServiceAccount = "";
                    string strSQLAgentServiceAccount = "";

                    try
                    {
                        SqlDataAdapter aSQLServerServiceAccount = new SqlDataAdapter(@"DECLARE @SQLServerServiceAccount varchar(100) EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', N'ObjectName', @SQLServerServiceAccount OUTPUT, N'no_output' SELECT @SQLServerServiceAccount AS SQLServerServiceAccount", conn1);
                        DataSet sSQLServerServiceAccount = new DataSet();
                        aSQLServerServiceAccount.Fill(sSQLServerServiceAccount);

                        //Get the ValueType of the second row of the recordset "ProductVersion"
                        DataRow drSQLServerServiceAccount = sSQLServerServiceAccount.Tables[0].Rows[0];

                        strSQLServerServiceAccount = drSQLServerServiceAccount["SQLServerServiceAccount"].ToString();
                    }
                    catch
                    {
                        strSQLServerServiceAccount = "Unknown";
                    }

                    try
                    {
                        SqlDataAdapter aSQLAgentServiceAccount = new SqlDataAdapter(@"DECLARE @SQLAgentServiceAccount varchar(100) EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT',N'ObjectName', @SQLAgentServiceAccount OUTPUT, N'no_output' SELECT @SQLAgentServiceAccount AS SQLAgentServiceAccount", conn1);
                        DataSet sSQLAgentServiceAccount = new DataSet();
                        aSQLAgentServiceAccount.Fill(sSQLAgentServiceAccount);

                        //Get the ValueType of the second row of the recordset "ProductVersion"
                        DataRow drSQLAgentServiceAccount = sSQLAgentServiceAccount.Tables[0].Rows[0];

                        strSQLAgentServiceAccount = drSQLAgentServiceAccount["SQLAgentServiceAccount"].ToString();
                    }
                    catch
                    {
                        strSQLAgentServiceAccount = "Unknown";
                    }  

                    SqlCommand cmd1 = cn.CreateCommand();
                    cmd1.CommandText = "UPDATE Instance SET InstanceVersion = '" + strInstanceVersion + "'" +
                                                       ",  InstanceCollation = '" + strInstanceCollation + "'" +
                                                       ",  InstanceEdition = '" + strInstanceEdition + "'" +
                                                       ",  InstanceServicePack = '" + strInstanceServicePack + "'" +
                                                       ",  InstanceLoginMode = '" + strInstanceLoginMode + "'" +
                                                       ",  InstanceMemory = '" + strInstanceMemory + "'" +
                                                       ",  InstanceMinMemory = '" + dr4[0].ToString() + "'" +
                                                       ",  InstanceMaxMemory = '" + dr5[0].ToString() + "'" +
                                                       ",  InstanceOSVersion = '" + Utility.GetWindowsVersion(MachineName.ToString()) + "'" +
                                                       ",  InstanceProcessorArchitecture = '" + strArchitecture + "'" +
                                                       ",  InstanceProcessorName = '" + strProcessorName + "'" +
                                                       ",  InstanceCoreCount = '" + strInstanceCoreCount + "'" +
                                                       ",  InstanceSocketCount = '" + strInstanceSocketCount + "'" +
                                                       ",  InstanceProcessorMaxClockSpeed = '" + strMaxClockSpeed + "'" +
                                                       ",  InstanceIsClustered = '" + strInstanceIsClustered + "'" +
                                                       ",  IsVirtual = '" + strIsVirtual.ToString() + "'" +                                                       
                                                       ",  InstancePortNumber = '" + dr1[2].ToString() + "'" +
                                                       ",  TraceFlags = '" + strTraceFlags.ToString() + "'" +
                                                       ",  LoadRepositoryLoadDate = '" + System.DateTime.Now.ToString() + "'" +
                                                       ",  InstanceDomainName = '" + dr1[3].ToString() + "'" +
                                                       ",  SQLServerServiceAccount = '" + strSQLServerServiceAccount.ToString() + "'" +
                                                       ",  SQLAgentServiceAccount = '" + strSQLAgentServiceAccount.ToString() + "'" +
                                                        " WHERE InstanceName = '" + row["InstanceName"].ToString() + "'";

                    cmd1.ExecuteNonQuery();

                    //Check for databases in the Repository that no longer exist on the server
                    SqlCommand cmdClearDBCheck = cn.CreateCommand();
                    SqlCommand cmdLoadLoadDBCheck = cn.CreateCommand();

                    cmdClearDBCheck.CommandText = @"DELETE FROM DBCheck WHERE InstanceName = '" + row["InstanceName"] + "'";
                    cmdClearDBCheck.ExecuteNonQuery();

                    SqlDataAdapter aDBCheck = new SqlDataAdapter(@"select '" + row["InstanceName"] + "' AS InstanceName,[name] AS DatabaseName FROM sysdatabases WITH(NOLOCK)", conn1);
                    DataSet sDBCheck = new DataSet();
                    aDBCheck.Fill(sDBCheck);

                    foreach (DataRow drDBCheck in sDBCheck.Tables[0].Rows)
                    {
                        cmdLoadLoadDBCheck.CommandText = @"INSERT INTO DBCheck (InstanceName, DatabaseName) VALUES ('" + drDBCheck["InstanceName"].ToString() + "', '" + drDBCheck["DatabaseName"].ToString() + "')";
                        cmdLoadLoadDBCheck.ExecuteNonQuery();
                    }

                    SqlDataAdapter aDBCheckDiff = new SqlDataAdapter(@"SELECT d.InstanceName,d.DatabaseName FROM [Database] d WHERE d.InstanceName = '" + row["InstanceName"].ToString() + "' AND d.DatabaseNAme NOT IN (SELECT c.DatabaseName FROM DBCheck c WHERE InstanceName = '" + row["InstanceName"].ToString() + "')", cn);
                    DataSet sDBCheckDiff = new DataSet();
                    aDBCheckDiff.Fill(sDBCheckDiff);

                    foreach (DataRow drDBCheckDiff in sDBCheckDiff.Tables[0].Rows)
                    {
                        //Remove the database from the repository
                        DbCommand dbCommand1 = db.GetSqlStringCommand("DELETE FROM [Database] WHERE InstanceName = '" + drDBCheckDiff["InstanceName"].ToString() + "' and DatabaseName = '" + drDBCheckDiff["DatabaseName"].ToString() + "'");
                        DbCommand dbCommand2 = db.GetSqlStringCommand("DELETE FROM [DatabaseNote] WHERE InstanceName = '" + drDBCheckDiff["InstanceName"].ToString() + "' and DatabaseName = '" + drDBCheckDiff["DatabaseName"].ToString() + "'");

                        db.ExecuteNonQuery(dbCommand1);
                        db.ExecuteNonQuery(dbCommand2);

                        String strAlertAddressesAvailable = db.ExecuteScalar(CommandType.Text, "SELECT COUNT(*) FROM [DBAContact] WHERE [DBAOnCallFlag] = 1 or [DBANotificationFlag] = 1 ").ToString();

                        if (strAlertAddressesAvailable == "0")
                        {
                            return;
                        }
                        else
                        {
                            MailMessage msg = new MailMessage();

                            msg.To.Add(Utility.DBANotificationEMailString());
                            msg.Subject = "DBA Alert: " + drDBCheckDiff["DatabaseName"].ToString() + " Database Deleted";
                            msg.Body = "-----------------------------------------------------" + " \r\n" +
                                       "       Alert   : Database Deleted From Repository" + " \r\n" +
                                       "-----------------------------------------------------" + " \r\n" +
                                       " SQL Instance  : " + drDBCheckDiff["InstanceName"].ToString() + " \r\n" +
                                       " Database      : " + drDBCheckDiff["DatabaseName"].ToString() + " \r\n" +
                                       " " + " \r\n" +
                                       "-----------------------------------------------------" + " \r\n" +
                                       " " + strTimeDisplay1.ToString().PadRight(13) + " : " + Time1DateTime.DateTime.ToString() + " \r\n" +
                                       " " + strTimeDisplay2.ToString().PadRight(13) + " : " + Time2DateTime.DateTime.ToString() + " \r\n" +
                                       " " + strTimeDisplay3.ToString().PadRight(13) + " : " + Time3DateTime.DateTime.ToString() + " \r\n" +
                                       " " + strTimeDisplay4.ToString().PadRight(13) + " : " + Time4DateTime.DateTime.ToString() + " \r\n" +
                                       "-----------------------------------------------------" + " \r\n" +
                                       "END OF MESSAGE" + " \r\n";

                            SmtpClient client = new SmtpClient();
                            client.Send(msg);
                        } 
                    }
                    //Load SQL Logins
                    SqlCommand cmdClearLogins = cn.CreateCommand();
                    SqlCommand cmdLoadLogins = cn.CreateCommand();

                    cmdClearLogins.CommandText = @"DELETE FROM LOGIN_SQL WHERE InstanceName = '" + row["InstanceName"] + "'";
                    cmdClearLogins.ExecuteNonQuery();

                    SqlDataAdapter aLOGIN_SQL = new SqlDataAdapter(@"select '" + row["InstanceName"] + "' AS InstanceName,[name] AS LOGINNAME,isntname,dbname AS default_database_name, createdate, updatedate FROM syslogins WITH(NOLOCK)", conn1);
                    DataSet sLOGIN_SQL = new DataSet();
                    aLOGIN_SQL.Fill(sLOGIN_SQL);

                    foreach (DataRow drLogins in sLOGIN_SQL.Tables[0].Rows)
                    {
                        cmdLoadLogins.CommandText = @"INSERT INTO LOGIN_SQL (InstanceName, LoginName,isntname, [default_database_name],create_date,modify_date) VALUES ('" + drLogins["InstanceName"].ToString() + "', '" + drLogins["LoginName"].ToString() + "', '" + drLogins["isntname"].ToString() + "', '" + drLogins["default_database_name"].ToString() + "', '" + drLogins["createdate"].ToString() + "', '" + drLogins["updatedate"].ToString() + "')";
                        cmdLoadLogins.ExecuteNonQuery();
                    } 
     
                    StringBuilder sb2005 = new StringBuilder();
                    sb2005.Append(@"DECLARE @dbname varchar(100) DECLARE @string varchar(2000) SET @string = '' create table #datafilestats  (Fileid tinyint,FileGroup1 tinyint,TotalExtents1 dec(15,2),UsedExtents1 dec(15,2),[name] varchar(1000),[FileName] varchar(1000)) create table #dbstats  (dbname varchar(100),DataSizeMB dec(15,2),DataSpaceUsedMB dec(15,2),DataSpaceAvailableMB dec(15,2)) create table #logstats  (dbname varchar(100),LogSizeMB dec(15,2), LogSpaceAvailableMB dec(15,2)) create table #indexstats (dbname varchar(100),IndexSpaceReservedMB dec(15,2), IndexSpaceUsedMB dec(15,2)) DECLARE dbnames_cursor CURSOR FOR SELECT '['+name+']' FROM master..sysdatabases WHERE status NOT IN (32,48,64,516,520,528,536,540,4624,4632,4636,66056,4194824,4194832,4194840,4198936,65568) OPEN dbnames_cursor FETCH NEXT FROM dbnames_cursor INTO @dbname WHILE (@@fetch_status = 0) BEGIN set @string = 'use ' + @dbname + ' DBCC SHOWFILESTATS' insert into #datafilestats  exec (@string) insert into #dbstats (dbname, DataSizeMB, DataSpaceUsedMB) select @dbname, sum(TotalExtents1)*65536.0/1048576.0,sum(UsedExtents1)*65536.0/1048576.0 from #datafilestats group by FileGroup1 update  #dbstats set DataSpaceAvailableMB = DataSizeMB - DataSpaceUsedMB where dbname = @dbname truncate table #datafilestats set @string = 'use ' + @dbname + ' SELECT '+''''+@dbname+''''+',CONVERT(DEC(15,2),size/128) AS LogSizeMB, CONVERT(DEC(15,2),size/128.0 - CAST(FILEPROPERTY(name, '+''''+'SpaceUsed'+''''+') AS int)/128.0) AS LogSpaceAvailableMB from sysfiles WHERE groupid = 0' insert into #logstats  exec (@string) update #logstats set LogSizeMB = 1 WHERE LogSizeMB = 0 set @string = 'use ' + @dbname + ' SELECT '+''''+@dbname+''''+',CONVERT(DEC(15,2),SUM(CAST(reserved AS BIGINT)*8)/1024) AS IndexSpaceReservedMB, CONVERT(DEC(15,2),SUM(CAST(used AS BIGINT)*8)/1024) AS IndexSpaceUsedMB from sysindexes WHERE indid >=1 and indid <=255 AND status NOT IN (8388704)' insert into #indexstats  exec (@string) FETCH NEXT FROM dbnames_cursor INTO @dbname END CLOSE dbnames_cursor DEALLOCATE dbnames_cursor drop table #datafilestats SELECT d.name as 'DatabaseName', s.DataSizeMB + l.LogSizeMB AS 'TotalSizeMB', s.DataSizeMB, s.DataSpaceUsedMB, s.DataSpaceAvailableMB, CONVERT(DEC(5,2),(s.DataSpaceAvailableMB / s.DataSizeMB) * 100) AS 'DataSpaceAvailablePct', l.LogSizeMB, CONVERT(DEC(15,2),(l.LogSizeMB-l.LogSpaceAvailableMB)) AS 'LogSpaceUsedMB', l.LogSpaceAvailableMB, CONVERT(DEC(5,2),(l.LogSpaceAvailableMB / l.LogSizeMB) * 100) AS 'LogSpaceAvailablePct', i.IndexSpaceReservedMB, i.IndexSpaceUsedMB, b1.backup_finish_date as 'LastBackupFULL',b2.backup_finish_date as 'LastBackupDIFF',b3.backup_finish_date as 'LastBackupLOG', d.state_desc as 'DatabaseStatus', d.is_read_only as 'IsReadOnly', d.recovery_model_desc AS 'RecoveryModel',  d.is_auto_shrink_on as 'AutoShrink', d.is_auto_update_stats_on as 'AutoUpdateStatistics', d.is_auto_create_stats_on as 'AutoCreateStatistics', d.is_auto_close_on as 'AutoClose', d.is_trustworthy_on  as 'Trustworthy', d.collation_name as 'Collation', d.[compatibility_level] as 'CompatibilityLevel', d.page_verify_option_desc as 'PageVerify', d.create_date as 'CreateDate' FROM sys.databases d LEFT JOIN (SELECT database_name,MAX(backup_finish_date) AS backup_finish_date FROM msdb..backupset WHERE type = 'D' GROUP BY database_name) AS b1 ON d.name = b1.database_name LEFT JOIN (SELECT database_name,MAX(backup_finish_date) AS backup_finish_date FROM msdb..backupset WHERE type = 'I' GROUP BY database_name) AS b2 ON d.name = b2.database_name LEFT JOIN (SELECT database_name,MAX(backup_finish_date) AS backup_finish_date FROM msdb..backupset WHERE type = 'L' GROUP BY database_name) AS b3 ON d.name = b3.database_name LEFT OUTER JOIN #dbstats s on s.dbname = '['+d.name+']' LEFT OUTER JOIN #logstats l on l.dbname = '['+d.name+']' LEFT OUTER JOIN #indexstats i on i.dbname = '['+d.name+']' GROUP BY d.name,d.state_desc,d.is_read_only,d.recovery_model_desc,d.is_auto_shrink_on,d.is_auto_update_stats_on,d.is_auto_create_stats_on,d.is_auto_close_on,d.is_trustworthy_on,d.collation_name,d.[compatibility_level],d.page_verify_option_desc,d.create_date,s.DataSizeMB,s.DataSpaceAvailableMB,s.DataSpaceUsedMB,l.LogSizeMB,l.LogSpaceAvailableMB,i.IndexSpaceReservedMB,i.IndexSpaceUsedMB,b1.backup_finish_date,b2.backup_finish_date,b3.backup_finish_date ORDER BY d.name drop table #dbstats drop table #logstats drop table #indexstats");
                                        
                    SqlConnection conn2005 = new SqlConnection(@"server=" + row["InstanceName"].ToString() + ";database=master;Integrated Security=SSPI;Pooling=False");
                                        
                    SqlDataAdapter a2005 = new SqlDataAdapter(sb2005.ToString(), conn2005);
                    DataSet s2005 = new DataSet();
                    a2005.SelectCommand.CommandTimeout = 600;
                    a2005.Fill(s2005);

                        foreach (DataRow dr2005 in s2005.Tables[0].Rows)
                        {
                            SqlCommand cmd2005 = cn.CreateCommand();
                            cmd2005.CommandText = "exec iuDatabase @InstanceName,@DatabaseName,@TotalSizeMB,@DataSizeMB,@DataSpaceUsedMB,@DataSpaceAvailableMB,@DataSpaceAvailablePct,@LogSizeMB,@LogSpaceUsedMB,@LogSpaceAvailableMB,@LogSpaceAvailablePct,@IndexSpaceReservedMB,@IndexSpaceUsedMB,@LastBackupFULL,@LastBackupDIFF,@LastBackupLOG,@DatabaseStatus,@IsReadOnly,@RecoveryModel,@AutoShrink,@AutoUpdateStatistics,@AutoCreateStatistics,@AutoClose,@Trustworthy,@Collation,@CompatibilityLevel,@PageVerify,@CreateDate";

                            cmd2005.Parameters.Add("@InstanceName", SqlDbType.VarChar, 50);
                            cmd2005.Parameters.Add("@DatabaseName", SqlDbType.VarChar, 100);
                            cmd2005.Parameters.Add("@TotalSizeMB", SqlDbType.Decimal);
                            cmd2005.Parameters.Add("@DataSizeMB", SqlDbType.Decimal);
                            cmd2005.Parameters.Add("@DataSpaceUsedMB", SqlDbType.Decimal);
                            cmd2005.Parameters.Add("@DataSpaceAvailableMB", SqlDbType.Decimal);
                            cmd2005.Parameters.Add("@DataSpaceAvailablePct", SqlDbType.Decimal);
                            cmd2005.Parameters.Add("@LogSizeMB", SqlDbType.Decimal);
                            cmd2005.Parameters.Add("@LogSpaceUsedMB", SqlDbType.Decimal);
                            cmd2005.Parameters.Add("@LogSpaceAvailableMB", SqlDbType.Decimal);
                            cmd2005.Parameters.Add("@LogSpaceAvailablePct", SqlDbType.Decimal);
                            cmd2005.Parameters.Add("@IndexSpaceReservedMB", SqlDbType.Decimal);
                            cmd2005.Parameters.Add("@IndexSpaceUsedMB", SqlDbType.Decimal);
                            cmd2005.Parameters.Add("@LastBackupFULL", SqlDbType.DateTime);
                            cmd2005.Parameters.Add("@LastBackupDIFF", SqlDbType.DateTime);
                            cmd2005.Parameters.Add("@LastBackupLOG", SqlDbType.DateTime);
                            cmd2005.Parameters.Add("@DatabaseStatus", SqlDbType.VarChar, 50);
                            cmd2005.Parameters.Add("@IsReadOnly", SqlDbType.Bit);
                            cmd2005.Parameters.Add("@RecoveryModel", SqlDbType.VarChar, 100);
                            cmd2005.Parameters.Add("@AutoShrink", SqlDbType.Bit);
                            cmd2005.Parameters.Add("@AutoUpdateStatistics", SqlDbType.Bit);
                            cmd2005.Parameters.Add("@AutoCreateStatistics", SqlDbType.Bit);
                            cmd2005.Parameters.Add("@AutoClose", SqlDbType.Bit);
                            cmd2005.Parameters.Add("@Trustworthy", SqlDbType.VarChar, 100);
                            cmd2005.Parameters.Add("@Collation", SqlDbType.VarChar, 100);
                            cmd2005.Parameters.Add("@CompatibilityLevel", SqlDbType.VarChar, 100);
                            cmd2005.Parameters.Add("@PageVerify", SqlDbType.VarChar, 100);
                            cmd2005.Parameters.Add("@CreateDate", SqlDbType.DateTime);

                            cmd2005.Parameters["@InstanceName"].Value = row["InstanceName"].ToString();
                            cmd2005.Parameters["@DatabaseName"].Value = dr2005["DatabaseName"].ToString();
                            cmd2005.Parameters["@TotalSizeMB"].Value = dr2005["TotalSizeMB"];
                            cmd2005.Parameters["@DataSizeMB"].Value = dr2005["DataSizeMB"];
                            cmd2005.Parameters["@DataSpaceUsedMB"].Value = dr2005["DataSpaceUsedMB"];
                            cmd2005.Parameters["@DataSpaceAvailableMB"].Value = dr2005["DataSpaceAvailableMB"];
                            cmd2005.Parameters["@DataSpaceAvailablePct"].Value = dr2005["DataSpaceAvailablePct"];
                            cmd2005.Parameters["@LogSizeMB"].Value = dr2005["LogSizeMB"];
                            cmd2005.Parameters["@LogSpaceUsedMB"].Value = dr2005["LogSpaceUsedMB"];
                            cmd2005.Parameters["@LogSpaceAvailableMB"].Value = dr2005["LogSpaceAvailableMB"];
                            cmd2005.Parameters["@LogSpaceAvailablePct"].Value = dr2005["LogSpaceAvailablePct"];
                            cmd2005.Parameters["@IndexSpaceReservedMB"].Value = dr2005["IndexSpaceReservedMB"];
                            cmd2005.Parameters["@IndexSpaceUsedMB"].Value = dr2005["IndexSpaceUsedMB"];
                            cmd2005.Parameters["@LastBackupFULL"].Value = dr2005["LastBackupFULL"];
                            cmd2005.Parameters["@LastBackupDIFF"].Value = dr2005["LastBackupDIFF"];
                            cmd2005.Parameters["@LastBackupLOG"].Value = dr2005["LastBackupLOG"];
                            cmd2005.Parameters["@DatabaseStatus"].Value = dr2005["DatabaseStatus"];
                            cmd2005.Parameters["@IsReadOnly"].Value = dr2005["IsReadOnly"];
                            cmd2005.Parameters["@RecoveryModel"].Value = dr2005["RecoveryModel"];
                            cmd2005.Parameters["@AutoShrink"].Value = dr2005["AutoShrink"];
                            cmd2005.Parameters["@AutoUpdateStatistics"].Value = dr2005["AutoUpdateStatistics"];
                            cmd2005.Parameters["@AutoCreateStatistics"].Value = dr2005["AutoCreateStatistics"];
                            cmd2005.Parameters["@AutoClose"].Value = dr2005["AutoClose"];
                            cmd2005.Parameters["@Trustworthy"].Value = dr2005["Trustworthy"];
                            cmd2005.Parameters["@Collation"].Value = dr2005["Collation"];
                            cmd2005.Parameters["@CompatibilityLevel"].Value = dr2005["CompatibilityLevel"];
                            cmd2005.Parameters["@PageVerify"].Value = dr2005["PageVerify"];
                            cmd2005.Parameters["@CreateDate"].Value = dr2005["CreateDate"];

                            cmd2005.ExecuteNonQuery();
                        }
                        conn2005.Close();
                    }
                
                //INSERT DATFILES HERE
                SqlCommand cmdClearDatabaseDataFiles = cn.CreateCommand();

                cmdClearDatabaseDataFiles.CommandText = @"DELETE FROM DatabaseFile WHERE InstanceName = '" + row["InstanceName"] + "'";
                cmdClearDatabaseDataFiles.ExecuteNonQuery();

                SqlConnection connDBFiles = new SqlConnection(@"server=" + row["InstanceName"].ToString() + ";database=master;Integrated Security=SSPI;Pooling=False");
                
                SqlDataAdapter aDBFiles = new SqlDataAdapter(@"select '" + row["InstanceName"].ToString() + "' AS InstanceName,db_name(dbid) as [DatabaseName],filename,size as filesize, maxsize as filemaxsize, growth as filegrowth, [name] as FileLogicalName from master.dbo.sysaltfiles", connDBFiles);
                DataSet sDBFiles = new DataSet();
                aDBFiles.SelectCommand.CommandTimeout = 600;
                aDBFiles.Fill(sDBFiles);

                foreach (DataRow drDBFiles in sDBFiles.Tables[0].Rows)
                {
                    SqlCommand cmdDBFiles = cn.CreateCommand();
                    cmdDBFiles.CommandText = "exec iuDatabaseFile @InstanceName,@DatabaseName,@FileName,@FileSize,@FileMaxSize,@FileGrowth,@FileLogicalName";

                    cmdDBFiles.Parameters.Add("@InstanceName", SqlDbType.VarChar, 50);
                    cmdDBFiles.Parameters.Add("@DatabaseName", SqlDbType.VarChar, 100);
                    cmdDBFiles.Parameters.Add("@FileName", SqlDbType.VarChar, 500);
                    cmdDBFiles.Parameters.Add("@FileSize", SqlDbType.Int);
                    cmdDBFiles.Parameters.Add("@FileMaxSize", SqlDbType.Int);
                    cmdDBFiles.Parameters.Add("@FileGrowth", SqlDbType.Int);
                    cmdDBFiles.Parameters.Add("@FileLogicalName", SqlDbType.VarChar, 500);

                    cmdDBFiles.Parameters["@InstanceName"].Value = drDBFiles["InstanceName"].ToString();
                    cmdDBFiles.Parameters["@DatabaseName"].Value = drDBFiles["DatabaseName"].ToString();
                    cmdDBFiles.Parameters["@FileName"].Value = drDBFiles["FileName"].ToString();
                    cmdDBFiles.Parameters["@FileSize"].Value = drDBFiles["FileSize"].ToString();
                    cmdDBFiles.Parameters["@FileMaxSize"].Value = drDBFiles["FileSize"].ToString();
                    cmdDBFiles.Parameters["@FileGrowth"].Value = drDBFiles["FileGrowth"].ToString();
                    cmdDBFiles.Parameters["@FileLogicalName"].Value = drDBFiles["FileLogicalName"].ToString();

                    cmdDBFiles.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                if (e.Message.Contains("timeout") | e.Message.Contains("0x80010002") | e.Message.Contains("Thread was being aborted") | e.Message.Contains("It is in the middle of a restore") | e.Message.Contains("cannot be opened. It is acting as a mirror database") | e.Message.Contains("Cannot insert duplicate key in object") | e.Message.Contains("An exception occurred in SMO while trying to manage a service") | e.Message.Contains("Incorrect syntax near 'System'") | e.Message.Contains("Incorrect syntax near 'DB'") | e.Message.Contains("Incorrect syntax near 'QDB'") | e.Message.Contains("Incorrect syntax near the keyword 'User'") | e.Message.Contains("Login failed for user") | e.Message.Contains("deadlock victim") | e.Message.Contains("arithmetic overflow") | e.Message.Contains("deadlock victim") | e.Message.Contains("Arithmetic overflow"))
                {
                    //return;
                }
                else
                {
                    SqlCommand cmdLoad5 = new SqlCommand();
                    cmdLoad5.CommandTimeout = 300;
                    cmdLoad5.Connection = cn;
                    cmdLoad5.CommandText = @"exec iuAlert @AlertPriority='High',@AlertInstanceName='" + row["InstanceName"].ToString() + "',@AlertName='" + e.Message.ToString().Replace("'", "''") + "'";
                    cmdLoad5.ExecuteNonQuery();
                }
            }

        SqlCommand cmdUpdateLastLoadRepositoryEnd = new SqlCommand();
        cmdUpdateLastLoadRepositoryEnd.CommandTimeout = 300;
        cmdUpdateLastLoadRepositoryEnd.Connection = cn;

        cmdUpdateLastLoadRepositoryEnd.CommandText = "UPDATE [Instance] SET LastLoadRepositoryEnd = getdate() WHERE InstanceName = '" + row["InstanceName"].ToString() + "'";
        cmdUpdateLastLoadRepositoryEnd.ExecuteNonQuery();
        }
        cn.Close();
    }
    public static void LoadInstanceStatus(string strInstanceList)
    {
        string connectionString = ConfigurationManager.AppSettings["Application_DataConnString"];
        Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");

        SqlConnection cn = new SqlConnection(connectionString);
        cn.Open();
        DataSet ds = new DataSet();

        if (strInstanceList == "All")
        {
            string EnableAlerts = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config WITH(NOLOCK) Where ConfigName ='EnableAlerts'").ToString();

            SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT InstanceName, InstanceVersion FROM Instance WITH(NOLOCK) WHERE LoadRepository = 'True' AND DashboardActive = 'True' AND LastLoadStatusEnd < DATEADD(minute, -5, GETDATE()) ORDER BY LastLoadStatusStart", cn);
            adapter.Fill(ds);            
        }
        else
        {
            SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT InstanceName, InstanceVersion FROM Instance WITH(NOLOCK) WHERE InstanceName = '" + strInstanceList.ToString() + "'", cn);
            adapter.Fill(ds);
        }
        foreach (DataRow drLoadInstanceStatus in ds.Tables[0].Rows)  //Each Instance
        {            
            string MachineName = String.Empty;
            string strInstanceName = drLoadInstanceStatus["InstanceName"].ToString();

            SqlCommand cmdUpdateLastLoadStatusStart = new SqlCommand();
            cmdUpdateLastLoadStatusStart.CommandTimeout = 300;
            cmdUpdateLastLoadStatusStart.Connection = cn;

            cmdUpdateLastLoadStatusStart.CommandText = "UPDATE [Instance] SET LastLoadStatusStart = getdate() WHERE InstanceName = '" + strInstanceName + "'";
            cmdUpdateLastLoadStatusStart.ExecuteNonQuery();

            if (drLoadInstanceStatus["InstanceName"].ToString().Contains(@","))
            {
                Int32 intLocation1 = drLoadInstanceStatus["InstanceName"].ToString().IndexOf(@",");
                MachineName = drLoadInstanceStatus["InstanceName"].ToString().Remove(intLocation1);
            }
            else
            {
                MachineName = drLoadInstanceStatus["InstanceName"].ToString();
            }
            try 
            {
                SqlConnection conn = new SqlConnection(@"server=" + strInstanceName + ";database=msdb;Integrated Security=SSPI;Pooling=False");
                SqlDataAdapter a = new SqlDataAdapter(@"select '" + strInstanceName + "' AS InstanceName,j.job_id, j.[name] AS 'JobName',s.last_outcome_message,SUBSTRING(CAST(s.last_run_date AS CHAR(8)),5,2) + '/' + RIGHT(CAST(s.last_run_date AS CHAR(8)),2) + '/' + LEFT(CAST(s.last_run_date AS CHAR(8)),4)+' '+LEFT(RIGHT('000000' + CAST(last_run_time AS VARCHAR(10)),6),2) + ':' + SUBSTRING(RIGHT('000000' + CAST(last_run_time AS VARCHAR(10)),6),3,2) + ':' + RIGHT(RIGHT('000000' + CAST(last_run_time AS VARCHAR(10)),6),2) AS 'LastRunDate' from sysjobs j WITH(NOLOCK) inner join sysjobservers s WITH(NOLOCK) on s.job_id = j.job_id where s.last_run_outcome IN (0,3) order by j.[name]", conn);
                DataSet s = new DataSet();
                a.Fill(s);
                conn.Close();

                //Clear Open Errors, Connection was successful so the following error can be auto-cleared
                //"A network-related or instance-specific error occurred while establishing a connection to SQL Server."
                //"The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)"
                //"The client was unable to establish a connection because of an error during connection initialization process before login"

                SqlCommand cmdAutoClearAlerts1 = new SqlCommand();
                cmdAutoClearAlerts1.CommandTimeout = 300;
                cmdAutoClearAlerts1.Connection = cn;
                cmdAutoClearAlerts1.CommandText = "DELETE FROM Alert WHERE AlertInstanceName = '" + strInstanceName + "' AND AlertName like 'A network-related or instance-specific error occurred while establishing a connection to SQL Server.%'";
                cmdAutoClearAlerts1.ExecuteNonQuery();

                SqlCommand cmdAutoClearAlerts2 = new SqlCommand();
                cmdAutoClearAlerts2.CommandTimeout = 300;
                cmdAutoClearAlerts2.Connection = cn;
                cmdAutoClearAlerts2.CommandText = "DELETE FROM Alert WHERE AlertInstanceName = '" + strInstanceName + "' AND AlertName Like 'The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)%'";
                cmdAutoClearAlerts2.ExecuteNonQuery();

                SqlCommand cmdAutoClearAlerts3 = new SqlCommand();
                cmdAutoClearAlerts3.CommandTimeout = 300;
                cmdAutoClearAlerts3.Connection = cn;
                cmdAutoClearAlerts3.CommandText = "DELETE FROM Alert WHERE AlertInstanceName = '" + strInstanceName + "' AND AlertName Like 'The client was unable to establish a connection because of an error during connection initialization process before login%'";
                cmdAutoClearAlerts3.ExecuteNonQuery();


                //Check for failed jobs
                SqlCommand cmdDeleteFailedJobs = new SqlCommand();
                SqlCommand cmdLoadFailedJobs = new SqlCommand();

                cmdDeleteFailedJobs.CommandTimeout = 300;
                cmdLoadFailedJobs.CommandTimeout = 300;
                            
                cmdDeleteFailedJobs.Connection = cn;
                cmdLoadFailedJobs.Connection = cn;
                        
                cmdDeleteFailedJobs.CommandText = "DELETE FROM FailedScheduledTasks WHERE InstanceName = '" + strInstanceName + "'";
                cmdDeleteFailedJobs.ExecuteNonQuery();

                

                foreach (DataRow dr3 in s.Tables[0].Rows)
                {
                    cmdLoadFailedJobs.CommandText = @"INSERT INTO FailedScheduledTasks (InstanceName, JobID, JobName, LastRunOutcome, LastRunDate) VALUES ('" + dr3["InstanceName"].ToString() + "', '" + dr3["Job_ID"].ToString() + "', '" + dr3["JobName"].ToString().Replace("'", "''") + "', '" + dr3["Last_Outcome_Message"].ToString().Replace("'", "''") + "', '" + dr3["LastRunDate"].ToString() + "')";
                    cmdLoadFailedJobs.ExecuteNonQuery();
                }

                //Check For Locked Out SQL Accounts
                CheckForLockedOutSQLAccounts(strInstanceName.ToString());  

                //Check for Waiting or Blocked Processes
                SqlCommand cmdDeleteWaitingOrBlockedProcesses = new SqlCommand();
                SqlCommand cmdLoadWaitingOrBlockedProcesses = new SqlCommand();
                SqlCommand cmdUpdateLastSQLRestart = new SqlCommand();

                cmdDeleteWaitingOrBlockedProcesses.CommandTimeout = 300;
                cmdLoadWaitingOrBlockedProcesses.CommandTimeout = 300;
                cmdUpdateLastSQLRestart.CommandTimeout = 300;

                cmdDeleteWaitingOrBlockedProcesses.Connection = cn;
                cmdLoadWaitingOrBlockedProcesses.Connection = cn;
                cmdUpdateLastSQLRestart.Connection = cn;

                cmdDeleteWaitingOrBlockedProcesses.CommandText = "DELETE FROM WaitingOrBlockedProcesses WHERE InstanceName = '" + strInstanceName + "'";
                cmdDeleteWaitingOrBlockedProcesses.ExecuteNonQuery();

                cmdUpdateLastSQLRestart.CommandText = "UPDATE [Instance] SET LastSQLServerRestart = (select crdate from master..sysdatabases where name='tempdb') WHERE InstanceName = '" + strInstanceName + "'";
                cmdUpdateLastSQLRestart.ExecuteNonQuery();

                SqlConnection conn1 = new SqlConnection(@"server=" + strInstanceName + ";database=master;Integrated Security=SSPI;Pooling=False");               

                SqlDataAdapter a1 = new SqlDataAdapter(@"select '" + strInstanceName + "' AS InstanceName,p.spid,p.status,p.blocked,d.name as databasename,p.cmd,p.cpu,p.physical_io,p.memusage,p.waittime,p.lastwaittype,last_batch=convert(varchar(26), p.last_batch,121),login_time=convert(varchar(26), p.login_time,121),p.hostname,p.loginame,p.program_name from master.dbo.sysprocesses p WITH(NOLOCK) INNER JOIN master.dbo.sysdatabases d WITH(NOLOCK) on p.dbid = d.dbid where (blocked!=0 and waittime > 300000 and spid >50 and cmd NOT LIKE '%BRKR TASK%') OR (waittime > 300000 and spid > 50 and cmd NOT LIKE '%BRKR TASK%')", conn1);
                SqlDataAdapter a2 = new SqlDataAdapter(@"select crdate from master..sysdatabases where name='tempdb'", conn1);
                DataSet s1 = new DataSet();
                DataSet s2 = new DataSet();
                a1.Fill(s1);
                a2.Fill(s2);
                conn1.Close();

                foreach (DataRow drLastSQLRestart in s2.Tables[0].Rows)
                {
                    cmdUpdateLastSQLRestart.CommandText = "UPDATE [Instance] SET LastSQLServerRestart = '" + drLastSQLRestart[0].ToString() + "' WHERE InstanceName = '" + strInstanceName + "'";
                    cmdUpdateLastSQLRestart.ExecuteNonQuery();
                }         

                foreach (DataRow drWaitingBlocked in s1.Tables[0].Rows)
                {
                    cmdLoadWaitingOrBlockedProcesses.CommandText = "INSERT INTO WaitingOrBlockedProcesses (InstanceName,spid,status,blocked,databasename,cmd,cpu,physical_io,memusage,waittime,lastwaittype,last_batch,login_time,hostname,loginame,program_name) VALUES ('" + drWaitingBlocked[0].ToString() + "', '" + drWaitingBlocked[1].ToString() + "', '" + drWaitingBlocked[2].ToString() + "', '" + drWaitingBlocked[3].ToString() + "', '" + drWaitingBlocked[4].ToString() + "', '" + drWaitingBlocked[5].ToString() + "', '" + drWaitingBlocked[6].ToString() + "', '" + drWaitingBlocked[7].ToString() + "', '" + drWaitingBlocked[8].ToString() + "', '" + drWaitingBlocked[9].ToString() + "', '" + drWaitingBlocked[10].ToString() + "', '" + drWaitingBlocked[11].ToString() + "', '" + drWaitingBlocked[12].ToString() + "', '" + drWaitingBlocked[13].ToString() + "', '" + drWaitingBlocked[14].ToString() + "', '" + drWaitingBlocked[15].ToString() + "')";
                    cmdLoadWaitingOrBlockedProcesses.ExecuteNonQuery();
                }
            }
            catch (Exception e)
                {
                    if (e.Message.Contains("timeout") | e.Message.Contains("0x80010002") | e.Message.Contains("0x80070005") | e.Message.Contains("0x800706BA") | e.Message.Contains("Thread was being aborted") | e.Message.Contains("It is in the middle of a restore") | e.Message.Contains("cannot be opened. It is acting as a mirror database") | e.Message.Contains("Cannot insert duplicate key in object") | e.Message.Contains("An exception occurred in SMO while trying to manage a service") | e.Message.Contains("Incorrect syntax near 'System'") | e.Message.Contains("Incorrect syntax near 'DB'") | e.Message.Contains("Incorrect syntax near 'QDB'") | e.Message.Contains("Incorrect syntax near the keyword 'User'") | e.Message.Contains("Object reference not set to an instance of an object") | e.Message.Contains("Login failed for user") | e.Message.Contains("deadlock victim") | e.Message.Contains("Arithmetic overflow"))
                    {
                        return;
                    }
                    else
                    {
                        SqlCommand cmdLoad5 = new SqlCommand();
                        cmdLoad5.CommandTimeout = 300;
                        cmdLoad5.Connection = cn;
                        cmdLoad5.CommandText = @"exec iuAlert @AlertPriority='High',@AlertInstanceName='" + strInstanceName.ToString() + "',@AlertName='"+e.Message.ToString().Replace("'","''")+"'";
                        cmdLoad5.ExecuteNonQuery();
                    }                   
                }         

        //Update  LastLoadStatusEnd
        SqlCommand cmdUpdateLastLoadStatusEnd = new SqlCommand();
        cmdUpdateLastLoadStatusEnd.CommandTimeout = 300;
        cmdUpdateLastLoadStatusEnd.Connection = cn;

        cmdUpdateLastLoadStatusEnd.CommandText = "UPDATE [Instance] SET LastLoadStatusEnd = getdate() WHERE InstanceName = '" + strInstanceName + "'";
        cmdUpdateLastLoadStatusEnd.ExecuteNonQuery();
        }
        cn.Close();        
    }
    public static void DBANotificationAlert(string stralert_message, string strInstance, string strPriority)
    {        
        Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");

        String strTimeDisplay1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1DisplayName'").ToString();
        String strTimeDisplay2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2DisplayName'").ToString();
        String strTimeDisplay3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3DisplayName'").ToString();
        String strTimeDisplay4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4DisplayName'").ToString();

        String strTimeZone1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1TimeZoneID'").ToString();
        String strTimeZone2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2TimeZoneID'").ToString();
        String strTimeZone3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3TimeZoneID'").ToString();
        String strTimeZone4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4TimeZoneID'").ToString();

        DateTimeOffset nowDateTime = DateTimeOffset.Now;
        DateTimeOffset Time1DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone1.ToString()));
        DateTimeOffset Time2DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone2.ToString()));
        DateTimeOffset Time3DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone3.ToString()));
        DateTimeOffset Time4DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone4.ToString()));

        //Set the strInstance to the an instancename when instance is not specified, without this the call will fail.
        if (strInstance.Length < 1)
        {
            strInstance = ConfigurationManager.AppSettings["Application_DataConnString"].ToString();            
        }
        string sqlQueryEnableAlerts = "SELECT ConfigValue FROM Config WHERE ConfigName = 'EnableAlerts'";
        String strEnableAlerts = db.ExecuteScalar(CommandType.Text, sqlQueryEnableAlerts).ToString();

        string sqlQueryBusinessCritical = "SELECT BusinessCritical FROM Instance WHERE InstanceName = '"+ strInstance +"'";
        String strBusinessCritical = db.ExecuteScalar(CommandType.Text, sqlQueryBusinessCritical).ToString();

        string sqlQueryBusinessCriticalLevel = "SELECT BusinessCriticalLevel FROM Instance WHERE InstanceName = '" + strInstance + "'";
        String strBusinessCriticalLevel = db.ExecuteScalar(CommandType.Text, sqlQueryBusinessCriticalLevel).ToString();

        if (strEnableAlerts == "True")
        {
            MailMessage msg = new MailMessage();

            if (strPriority == "High")
            {
                msg.To.Add(Utility.DBANotificationEMailString().ToString());
                if (strBusinessCritical.ToString() == "True")
                {
                    msg.CC.Add(Utility.DBAOnCallNotificationEMailString().ToString());
                    msg.Priority = MailPriority.High;
                    msg.Subject = "DBA Alert: " + strInstance.ToString() + " " + strBusinessCriticalLevel.ToString();
                    msg.Body = "--------------------------------------------------------- " + " \r\n" +
                               "Instance Name     : " + strInstance.ToString() + " \r\n" +
                               "Business Critical : " + strBusinessCriticalLevel.ToString() + " \r\n" +
                               "--------------------------------------------------------- " + " \r\n" +
                               stralert_message.ToString() + " \r\n" +
                               " " + " \r\n" +
                               "---------------------------------------------------------" + " \r\n" +
                               " " + strTimeDisplay1.ToString().PadRight(13) + " : " + Time1DateTime.DateTime.ToString() + " \r\n" +
                               " " + strTimeDisplay2.ToString().PadRight(13) + " : " + Time2DateTime.DateTime.ToString() + " \r\n" +
                               " " + strTimeDisplay3.ToString().PadRight(13) + " : " + Time3DateTime.DateTime.ToString() + " \r\n" +
                               " " + strTimeDisplay4.ToString().PadRight(13) + " : " + Time4DateTime.DateTime.ToString() + " \r\n" +
                               "--------------------------------------------------------- " + " \r\n" +
                               "END OF ALERT";
                }
                else
                {
                    msg.Priority = MailPriority.High;
                    msg.Subject = "DBA Alert: " + strInstance.ToString();
                    msg.Body = "--------------------------------------------------------- " + " \r\n" +
                               "Instance Name  : " + strInstance.ToString() + " \r\n" +
                               "--------------------------------------------------------- " + " \r\n" +
                               stralert_message.ToString() + " \r\n" +
                               " " + " \r\n" +
                               "--------------------------------------------------------- " + " \r\n" +
                               " " + strTimeDisplay1.ToString().PadRight(13) + " : " + Time1DateTime.DateTime.ToString() + " \r\n" +
                               " " + strTimeDisplay2.ToString().PadRight(13) + " : " + Time2DateTime.DateTime.ToString() + " \r\n" +
                               " " + strTimeDisplay3.ToString().PadRight(13) + " : " + Time3DateTime.DateTime.ToString() + " \r\n" +
                               " " + strTimeDisplay4.ToString().PadRight(13) + " : " + Time4DateTime.DateTime.ToString() + " \r\n" +
                               "--------------------------------------------------------- " + " \r\n" +
                               "END OF ALERT";
                }
            }
            else if (strPriority == "Medium")
            {
                msg.To.Add(Utility.DBANotificationEMailString());
                msg.Priority = MailPriority.Normal;
                msg.Subject = "DBA Notification: " + strInstance.ToString();
                msg.Body = "--------------------------------------------------------- " + " \r\n" +
                           "Instance Name  : " + strInstance.ToString() + " \r\n" +
                           "--------------------------------------------------------- " + " \r\n" +
                           stralert_message.ToString() + " \r\n" +
                           " " + " \r\n" +
                           "--------------------------------------------------------- " + " \r\n" +
                           " " + strTimeDisplay1.ToString().PadRight(13) + " : " + Time1DateTime.DateTime.ToString() + " \r\n" +
                           " " + strTimeDisplay2.ToString().PadRight(13) + " : " + Time2DateTime.DateTime.ToString() + " \r\n" +
                           " " + strTimeDisplay3.ToString().PadRight(13) + " : " + Time3DateTime.DateTime.ToString() + " \r\n" +
                           " " + strTimeDisplay4.ToString().PadRight(13) + " : " + Time4DateTime.DateTime.ToString() + " \r\n" +
                           "--------------------------------------------------------- " + " \r\n" +
                           "END OF ALERT";
            }
            else if (strPriority == "Low")
            {
                msg.Subject = "DBA Notification: " + strInstance.ToString();
                msg.To.Add(Utility.DBANotificationEMailString());
                msg.Priority = MailPriority.Low;
                msg.Body = "--------------------------------------------------------- " + " \r\n" +
                           "Instance Name  : " + strInstance.ToString() + " \r\n" +
                           "--------------------------------------------------------- " + " \r\n" +
                           stralert_message.ToString() + " \r\n" +
                           " " + " \r\n" +
                           "--------------------------------------------------------- " + " \r\n" +
                           " " + strTimeDisplay1.ToString().PadRight(13) + " : " + Time1DateTime.DateTime.ToString() + " \r\n" +
                           " " + strTimeDisplay2.ToString().PadRight(13) + " : " + Time2DateTime.DateTime.ToString() + " \r\n" +
                           " " + strTimeDisplay3.ToString().PadRight(13) + " : " + Time3DateTime.DateTime.ToString() + " \r\n" +
                           " " + strTimeDisplay4.ToString().PadRight(13) + " : " + Time4DateTime.DateTime.ToString() + " \r\n" +
                           "--------------------------------------------------------- " + " \r\n" +
                           "END OF ALERT";
            }
            try
            {
                SmtpClient client = new SmtpClient();
                client.Send(msg);
            }
            catch
            {
                MessageBox.Show("There was a problem delivering this email alert.  Ensure that your SMTP Server is configured correctly.");
            }                       
        }
    }
    public static void DBAAlertEMailSummary()
    {
        Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");

        String strTimeDisplay1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1DisplayName'").ToString();
        String strTimeDisplay2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2DisplayName'").ToString();
        String strTimeDisplay3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3DisplayName'").ToString();
        String strTimeDisplay4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4DisplayName'").ToString();

        String strTimeZone1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1TimeZoneID'").ToString();
        String strTimeZone2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2TimeZoneID'").ToString();
        String strTimeZone3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3TimeZoneID'").ToString();
        String strTimeZone4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4TimeZoneID'").ToString();

        DateTimeOffset nowDateTime = DateTimeOffset.Now;
        DateTimeOffset Time1DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone1.ToString()));
        DateTimeOffset Time2DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone2.ToString()));
        DateTimeOffset Time3DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone3.ToString()));
        DateTimeOffset Time4DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone4.ToString()));

        DbCommand dbCommand = db.GetSqlStringCommand("SELECT a.AlertInstanceName As 'InstanceName', LEFT(a.AlertName,40) AS 'Alert', CONVERT (CHAR(19), a.AlertLastOccurance) as 'LastOccurance' FROM Alert a INNER JOIN Instance i on i.InstanceName = a.AlertInstanceName WHERE AlertStatus = 'Open' AND i.BusinessCritical = 1");
        DataSet ds = db.ExecuteDataSet(dbCommand);

        DataTable dt = ds.Tables[0];

        if (dt.Rows.Count >= 1)
        {
            StringBuilder sb = new StringBuilder(8192, 8192);

            foreach (DataRow myrow in dt.Rows)
            {
                sb.Append(@"  " + myrow[2].ToString().Replace("\"", "\\") + " - " + myrow[0].ToString().Replace("\"", "\\") + " " + myrow[1].ToString() + " \r\n");
            }

            MailMessage msg = new MailMessage();
            msg.To.Add(Utility.DBANotificationEMailString().ToString());
            msg.CC.Add(Utility.DBAOnCallNotificationEMailString().ToString());
            msg.Subject = "DBA Alert: OPEN ALERT SUMMARY (BUSINESS CRITICAL)";
            msg.BodyEncoding = Encoding.UTF8;
            msg.Body = "--------------------------------------------------------- " + " \r\n" +
                       "DBA Alert   : OPEN ALERT SUMMARY (BUSINESS CRITICAL)" + " \r\n" +
                       "--------------------------------------------------------- " + " \r\n" +
                       "The following BUSINESS CRITICAL Alerts are OPEN and NOT Acknowledged: " + "\r\n" +
                       "--------------------------------------------------------- " + " \r\n" +
                       sb.Append(System.Environment.NewLine) +
                       " " + " \r\n" +
                       "--------------------------------------------------------- " + " \r\n" +
                       " " + strTimeDisplay1.ToString().PadRight(13) + " : " + Time1DateTime.DateTime.ToString() + " \r\n" +
                       " " + strTimeDisplay2.ToString().PadRight(13) + " : " + Time2DateTime.DateTime.ToString() + " \r\n" +
                       " " + strTimeDisplay3.ToString().PadRight(13) + " : " + Time3DateTime.DateTime.ToString() + " \r\n" +
                       " " + strTimeDisplay4.ToString().PadRight(13) + " : " + Time4DateTime.DateTime.ToString() + " \r\n" +
                       "--------------------------------------------------------- " + " \r\n" +
                       "END OF ALERT";

            try
            {
                SmtpClient client = new SmtpClient();
                client.Send(msg);
            }
            catch
            {
                MessageBox.Show("There was a problem delivering this email alert.  Ensure that your SMTP Server is configured correctly.");
            }
        }
    }
    public static void CheckForLockedOutSQLAccounts(string strInstanceName)
    {
        Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");
        string connectionString = ConfigurationManager.AppSettings["Application_DataConnString"];

        String strTimeDisplay1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1DisplayName'").ToString();
        String strTimeDisplay2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2DisplayName'").ToString();
        String strTimeDisplay3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3DisplayName'").ToString();
        String strTimeDisplay4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4DisplayName'").ToString();

        String strTimeZone1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1TimeZoneID'").ToString();
        String strTimeZone2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2TimeZoneID'").ToString();
        String strTimeZone3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3TimeZoneID'").ToString();
        String strTimeZone4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4TimeZoneID'").ToString();

        DateTimeOffset nowDateTime = DateTimeOffset.Now;
        DateTimeOffset Time1DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone1.ToString()));
        DateTimeOffset Time2DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone2.ToString()));
        DateTimeOffset Time3DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone3.ToString()));
        DateTimeOffset Time4DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone4.ToString()));

        DbCommand dbCommand3 = db.GetSqlStringCommand("DELETE FROM [LOGIN_SQL] WHERE InstanceName = '" + strInstanceName.ToString() + "'");
        db.ExecuteNonQuery(dbCommand3);

        SqlConnection cn = new SqlConnection(connectionString);
        cn.Open();

        //Check For Locked Out SQL Accounts
        SqlCommand cmdUpdateLockedOutAccounts = new SqlCommand();
        SqlCommand cmdLoadLogins = cn.CreateCommand();
        cmdUpdateLockedOutAccounts.CommandTimeout = 300;
        cmdUpdateLockedOutAccounts.Connection = cn;

        SqlConnection connLOGIN_SQL = new SqlConnection(@"server=" + strInstanceName + ";database=master;Integrated Security=SSPI;Pooling=False");               
             
        SqlDataAdapter aLOGIN_SQL = new SqlDataAdapter(@"select '" + strInstanceName + "' AS InstanceName, [name] AS LOGINNAME, LOGINPROPERTY([name],'isExpired') AS isExpired,LOGINPROPERTY([name],'IsLocked') AS IsLocked,LOGINPROPERTY([name],'LockoutTime') AS LockoutTime,is_policy_checked, is_expiration_checked,[default_database_name],create_date, modify_date FROM sys.sql_logins WITH(NOLOCK) WHERE LOGINPROPERTY([name],'isExpired') = 1 or LOGINPROPERTY([name],'IsLocked') = 1", connLOGIN_SQL);
        SqlDataAdapter aLOGIN_SQL2 = new SqlDataAdapter(@"select '" + strInstanceName + "' AS InstanceName,[name] AS LOGINNAME,isntname,dbname AS default_database_name, createdate, updatedate FROM syslogins WITH(NOLOCK)", connLOGIN_SQL);
        DataSet sLOGIN_SQL = new DataSet();
        DataSet sLOGIN_SQL2 = new DataSet();
        aLOGIN_SQL.Fill(sLOGIN_SQL);
        aLOGIN_SQL2.Fill(sLOGIN_SQL2);
        connLOGIN_SQL.Close();

        foreach (DataRow dr1 in sLOGIN_SQL2.Tables[0].Rows)
        {
            cmdLoadLogins.CommandText = @"INSERT INTO LOGIN_SQL (InstanceName, LoginName,isntname,[default_database_name],create_date,modify_date) VALUES ('" + dr1["InstanceName"].ToString() + "', '" + dr1["LoginName"].ToString() + "', '" + dr1["isntname"].ToString() + "', '" + dr1["default_database_name"].ToString() + "', '" + dr1["createdate"].ToString() + "', '" + dr1["updatedate"].ToString() + "')";
            cmdLoadLogins.ExecuteNonQuery();
        }
    
        foreach (DataRow dr1 in sLOGIN_SQL.Tables[0].Rows)
        {          
            if (dr1["isLocked"].ToString() == "1" | dr1["isExpired"].ToString() == "1")
            {
                cmdUpdateLockedOutAccounts.CommandText = "UPDATE LOGIN_SQL SET isExpired = '" + dr1["isExpired"].ToString() + "', IsLocked = '" + dr1["isLocked"].ToString() + "', LockoutTime = '" + dr1["LockoutTime"].ToString() + "', is_policy_checked = '" + dr1["is_policy_checked"].ToString() + "', is_expiration_checked = '" + dr1["is_expiration_checked"].ToString() + "', [default_database_name] = '" + dr1["default_database_name"].ToString() + "', create_date = '" + dr1["create_date"].ToString() + "', modify_date = '" + dr1["modify_date"].ToString() + "' WHERE InstanceName = '" + dr1["InstanceName"].ToString() + "' AND loginname = '" + dr1["loginname"].ToString() + "'";
                cmdUpdateLockedOutAccounts.ExecuteNonQuery();

                MailMessage msg = new MailMessage();

                msg.To.Add(Utility.DBANotificationEMailString());
                msg.Subject = "DBA Alert: SQL Account LOCKED OUT or EXPIRED";
                msg.Body = "-----------------------------------------------------" + " \r\n" +
                   "       Alert   : SQL Account LOCKED OUT or EXPIRED" + " \r\n" +
                   "-----------------------------------------------------" + " \r\n" +
                   " SQL Instance  : " + dr1["InstanceName"].ToString() + " \r\n" +
                   "  SQL Account  : " + dr1["loginname"].ToString() + " \r\n" +
                   " " + " \r\n" +
                   "-----------------------------------------------------" + " \r\n" +
                   " " + strTimeDisplay1.ToString().PadRight(13) + " : " + Time1DateTime.DateTime.ToString() + " \r\n" +
                   " " + strTimeDisplay2.ToString().PadRight(13) + " : " + Time2DateTime.DateTime.ToString() + " \r\n" +
                   " " + strTimeDisplay3.ToString().PadRight(13) + " : " + Time3DateTime.DateTime.ToString() + " \r\n" +
                   " " + strTimeDisplay4.ToString().PadRight(13) + " : " + Time4DateTime.DateTime.ToString() + " \r\n" +
                   "-----------------------------------------------------" + " \r\n" +
                   "END OF MESSAGE" + " \r\n";
                try
                {
                    SmtpClient client = new SmtpClient();
                    client.Send(msg);
                }
                catch
                {
                    MessageBox.Show("There was a problem delivering this email alert.  Ensure that your SMTP Server is configured correctly.");
                }               
            }            
        }
    }    
}